from industrialai.db import model as db_model
import logging
import math
import os
from pecan import abort
from sqlalchemy import create_engine, func
from sqlalchemy.orm import exc
from sqlalchemy.orm import sessionmaker

logger = logging.getLogger(__name__)
Domain = "sqlalchemy"


_ENGINE = None
_SESSION_MAKER = None


def get_engine():
    global _ENGINE
    if _ENGINE is not None:
        return _ENGINE
    DB_URL = (
        os.getenv("db_url") or
        "sqlite:///C:\\Users\\23360\\Desktop\\dhuProject\\pecan-swagger\\"
        "examples\\industrial-ai-apiserver\\"
        "industrialai.db?check_same_thread=False "
    )
    _ENGINE = create_engine(DB_URL, echo=True)
    return _ENGINE


def get_session_maker(engine):
    global _SESSION_MAKER
    if _SESSION_MAKER is not None:
        return _SESSION_MAKER
    _SESSION_MAKER = sessionmaker(bind=engine)
    return _SESSION_MAKER


def get_session():
    engine = get_engine()
    maker = get_session_maker(engine)
    session = maker()
    return session


class Connection(object):
    def __init__(self):
        pass

    def get_user(self, user_id):
        user = None
        query = get_session().query(db_model.User).filter_by(id=user_id)
        try:
            user = query.one()
        except exc.NoResultFound:
            logger.error("query by enterpriseId not found ...")
            abort(404)
        return user

    def list_users(self, page_num, page_size, **kwargs):
        users = []
        # query = get_session().query(db_model.User.account,
        # db_model.User.name, db_model.User.roleId,
        # db_model.User.status, db_model.User.create_at, db_model.User.source)
        query = get_session().query(db_model.User)
        for k, v in kwargs.items():
            if v:
                if k == "name":
                    query = query.filter(
                        db_model.User.name.like("%{keyword}%".
                                                format(keyword=v))
                    )
                elif k == "account":
                    query = query.filter(
                        db_model.User.account.like("%{keyword}%".
                                                   format(keyword=v))
                    )
                else:
                    query = query.filter_by(**{k: v})
        try:
            users = query.slice((page_num-1)*page_size, page_size).all()
            num = query.count()
            pages = math.ceil(num/page_size)
            for item in users:
                item.pwd = None
        except exc.NoResultFound:
            logger.error("query all user occur error ...")
            abort(404)
        return users, pages

    def update_user(self, user):
        id = user.get("id")
        logger.info("user.id: %s" % (id))
        try:
            session = get_session()
            session.query(db_model.User).filter_by(id=id).one()
            for k, v in user.items():
                if v is not None:
                    session.query(db_model.User).filter_by(id=id). \
                        update({k: v})
            session.flush()
            session.commit()
        except exc.NoResultFound:
            logger.error("update user occur error ...")
            abort(404)

        return user

    def delete_user(self, user_id):
        logger.info("user.id: %s" % (user_id))
        try:
            session = get_session()
            user = session.query(db_model.User).filter_by(id=user_id).first()
            session.delete(user)
            session.flush()
            session.commit()
        except exc.NoResultFound:
            logger.error("delete user occur error ...")
            abort(404)

    def add_user(self, user):
        db_user = db_model.User(
            name=user.get("name"),
            account=user.get("account"),
            pwd=user.get("pwd"),
            phone=user.get("phone"),
            roleId=user.get("roleId"),
            enterpriseId=user.get("enterpriseId"),
            status=user.get("status"),
        )
        try:
            session = get_session()
            if not user.get("groupId") is None:
                group = (
                    session.query(db_model.Group)
                    .filter_by(id=user.get("groupId"))
                    .one()
                )
                db_user.group = group
            session.add(db_user)
            session.flush()
            session.commit()
        except exc.NoResultFound:
            logger.error("add user occour error ...")
            abort(404)
        # return session.query(db_model.Group).options(
        # joinedload(db_model.Group.users)).all()

    def list_roles(self, page_num, page_size):
        roles = []
        query = get_session().query(db_model.Role)
        try:
            roles = query.slice((page_num-1)*page_size, page_size).all()
            num = get_session().query(func.count(db_model.Role.id)).scalar()
            pages = math.ceil(num/page_size)
        except exc.NoResultFound:
            logger.error("query all roles occur error ...")
            abort(404)
        return roles, pages
